import pandas as pd
from openpyxl import Workbook


# 统计各班不及格门数
def countFailed(path):
    data = pd.read_excel(path)
    # 使用groupby和agg函数进行分班级统计
    result = data.groupby(['班级名称', '不及格门数']).size().unstack(fill_value=0)

    # 重命名列名
    result.columns = [f'{i}门不及格' for i in result.columns]

    # 添加一列统计3门及以上不及格的人数
    result['3门及以上不及格'] = result.iloc[:, 2:].sum(axis=1)

    result = result[['1门不及格', '2门不及格', '3门及以上不及格']]

    result = result.sort_values(by='班级名称', ascending=True)

    return result


def getAverageOfGPA(path):
    data = pd.read_excel(path)
    # 根据班级和专业分组，并计算平均绩点
    average_gpa_by_class = data.groupby('班级名称')['平均学分绩点'].mean().reset_index()

    # 四舍五入保留两位小数
    average_gpa_by_class['平均学分绩点'] = average_gpa_by_class['平均学分绩点'].round(2)

    result = average_gpa_by_class.sort_values(by='班级名称', ascending=True)

    return result


def countClsProfsRank(path):
    data = pd.read_excel(path)
    data['profs_rank'] = data.groupby('专业名称')['平均学分绩点'].rank(ascending=False)
    data['profs_count'] = data.groupby('专业名称')['姓名'].transform('count')  # 假设学生姓名列用于计算总人数

    # 计算'profs_rank'/'profs_count'
    data['profs_ratio'] = data['profs_rank'] / data['profs_count']

    top10 = data['profs_rank'] <= 10
    top30percent = data['profs_ratio'] <= 0.3
    top50percent = data['profs_ratio'] <= 0.5
    bottom30percent = data['profs_ratio'] >= 0.7
    result = {
        '专业前10人数': data[top10].groupby('班级名称')['profs_rank'].count(),
        '专业前30%人数': data[top30percent].groupby('班级名称')['profs_rank'].count(),
        '专业前50%人数': data[top50percent].groupby('班级名称')['profs_rank'].count(),
        '专业后30%人数': data[bottom30percent].groupby('班级名称')['profs_rank'].count()
    }
    # 合并result中的数据
    merged_data = pd.concat(result.values(), axis=1)

    # 给每一列取一个合适的列名，可以根据需要进行修改
    merged_data.columns = ['专业前10人数', '专业前30%人数', '专业前50%人数', '专业后30%人数']

    # 将NaN值替换为0
    merged_data.fillna(0, inplace=True)

    # 将"专业前10人数"列的数据类型更改为整数
    merged_data["专业前10人数"] = merged_data["专业前10人数"].astype(int)

    result = merged_data.sort_values(by='班级名称', ascending=True)

    return result


def getProfsGPAMin(path):
    data = pd.read_excel(path)

    # 按专业分组
    grouped = data.groupby('专业名称')

    # 创建一个空的DataFrame，用于存储统计结果
    result_df = pd.DataFrame(columns=['专业名称', '前10名绩点最小值', '前30%绩点最小值', '前50%绩点最小值'])

    # 遍历每个专业
    for group_name, group_data in grouped:
        top_10 = group_data.nlargest(10, '平均学分绩点')  # 获取前十名学生
        top_30_percent = group_data.nlargest(int(len(group_data) * 0.3), '平均学分绩点')  # 获取前30%的学生
        top_50_percent = group_data.nlargest(int(len(group_data) * 0.5), '平均学分绩点')  # 获取前50%的学生

        # 计算绩点最小值
        min_top_10 = top_10['平均学分绩点'].min()
        min_top_30_percent = top_30_percent['平均学分绩点'].min()
        min_top_50_percent = top_50_percent['平均学分绩点'].min()

        # 将统计结果添加到结果DataFrame中
        # 将统计结果添加到结果DataFrame中
        result_df = pd.concat([result_df, pd.DataFrame(
            {
                '专业名称': [group_name],
                '前10名绩点最小值': [min_top_10],
                '前30%绩点最小值': [min_top_30_percent],
                '前50%绩点最小值': [min_top_50_percent]
            })], ignore_index=True).sort_values(by='专业名称', ascending=True)

    return result_df


if __name__ == '__main__':
    # 创建一个 ExcelWriter 对象，指定输出文件名
    excel_writer = pd.ExcelWriter('数据/成绩分析.xlsx', engine='xlsxwriter')

    countClsProfsRank('数据/6学期绩点信息.xls').to_excel(excel_writer, sheet_name='排名分布情况')
    getAverageOfGPA('数据/6学期绩点信息.xls').to_excel(excel_writer, sheet_name='各专业平均绩点', index=False)
    countFailed('数据/6学期不及格成绩列表.xls').to_excel(excel_writer, sheet_name='各班挂科人数明细')
    getProfsGPAMin('数据/6学期绩点信息.xls').to_excel(excel_writer, sheet_name='专业绩点分段值', index=False)

    excel_writer.close()

    print('文件已生成!')
